BigQuery リモート関数で Natural Language API を使って、SQL でお客様レビューの感情分析してみた。

みかみ

2022.05.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

Google Cloud 認定 Data Engineer の勉強してたら思ってた以上に ML 要素が強そうで現実逃避したくなってきたので、大好きな BigQuery と遊ぶことにしました。。（ML わけがわからないよ。

はじめに

リモート関数とは

BigQuery から SQL を実行する時に呼び出せる外部関数で、ユーザー定義関数（UDF）と同じように使えます。 API コールなど、BigQuery 内で完結できない処理もリモート関数を使えば実装できちゃいます。

2022/05/20 現在、まだプレビューの機能ですので、利用する際にはご留意ください。

Natural Language API とは

Google Cloud でトレーニング済みの機械学習モデルを、API をコールするだけで利用できるサービスです。文章にどんな内容が含まれるのか判別できるエンティティ分析や、その文章がネガティブなのかポジティブなのか判断できる感情分析が、APIのリクエストパラメータで文字列を渡すだけでお手軽に実行できてしまいます。

下記サイトの「Natural Language API のデモ」から、ブラウザ上でもお試しできます。（おもしろいので是非試してみて下さい

Natural Language AI | Cloud Natural Language ドキュメント

また、以下の記事でも詳しく紹介していますので、是非ご参照ください。

GCPの「Natural Language API」を試してみた | DevelopersIO

やりたいこと

BigQuery のリモート関数をつくってみたい
Natural Language API を Python コードからたたいてみたい
SQL 実行で感情分析結果を取得したい

前提

Google Cloud SDK（bq コマンド）の実行環境は準備済みであるものとします。本エントリでは、Cloud Shell を使用ました。

Cloud Shell の使用｜ Cloud Shell ドキュメント

サンプルデータを準備

ネットショップの商品レビューを感情分析するユースケースを想定しています。

ユーザー ID、ショップ番号、レビュー内容、レビュー投稿日時カラムを持つ、以下のテーブルを BigQuery に作成しました。

Cloud Functions 関数を作成

Cloud Functions で、SQL 実行時に呼び出される関数を作成します。

HTTP トリガーで、以下の Python コードを実行する Cloud Functions 関数を get_sentiment_sample という名前で作成します。

from google.cloud import language_v1
import json

def analyze_sentiment(text):
    client = language_v1.LanguageServiceClient()

    document = language_v1.Document(
        content=text, type_=language_v1.Document.Type.PLAIN_TEXT
    )

    sentiment = client.analyze_sentiment(
        request={"document": document}
    ).document_sentiment

    print("Text: {}".format(text))
    print("Sentiment: {}, {}".format(sentiment.score, sentiment.magnitude))

    return {'text': text, 'score': sentiment.score, 'magnitude': sentiment.magnitude}

def get_sentiment(request):
    try:
        return_value = []
        request_json = request.get_json()
        calls = request_json['calls']
        for call in calls:
            for text in call:
                return_value.append(analyze_sentiment(text))
        replies = [str(x) for x in return_value]
        return_json = json.dumps( { "replies" :  replies} )
        print(return_json)
        return return_json
    except Exception as inst:
        return json.dumps( { "errorMessage": 'something unexpected in input' } ), 400

Natural Language API 用のパッケージインストールが必要なため、以下の requirements.txt と合わせてデプロイしました。

google-cloud-language>=2.4.1

Natural Language API リクエスト、BigQuery リモート関数処理用の Python コードは、下記ドキュメントのサンプルコードを参照させていただきました。

CLOUD_RESOURCE 接続を作成

Cloud Shell から以下のコマンドを実行し、BigQuery の CLOUD_RESOURCE 接続を作成しました。

bq mk --connection --display_name='natural language api test' --connection_type=CLOUD_RESOURCE --project_id=cm-da-mikami-yuki-258308 --location=asia-northeast1 remote-function-test

作成したコネクションを bq show コマンドで確認し、リモート関数実行時に使用されるサービスアカウントを確認します。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq show --location=asia-northeast1 --connection remote-function-test
Connection cm-da-mikami-yuki-258308.asia-northeast1.remote-function-test

                        name                                friendlyName          description    Last modified         type        hasCredential                                               properties
 --------------------------------------------------- --------------------------- ------------- ----------------- ---------------- --------------- -----------------------------------------------------------------------------------------------------
  797147019523.asia-northeast1.remote-function-test   natural language api test                 20 May 08:46:29   CLOUD_RESOURCE   False           {"serviceAccountId": "connection-xxxxxxxxxxxx-hos1@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}

※サービスアカウント ID は一部伏せ字に変更しています。

先ほど作成した Cloud Functions に、確認したサービスアカウントでの関数実行権限を付与しました。

リモート関数を作成

BigQuery コンソールから SQL を実行し、リモート関数を作成しました。

CREATE OR REPLACE FUNCTION dataset_1.get_sentiment_sample(value STRING) RETURNS STRING
REMOTE WITH CONNECTION `cm-da-mikami-yuki-258308.asia-northeast1.remote-function-test`
    OPTIONS (
        endpoint = 'https://asia-northeast1-cm-da-mikami-yuki-258308.cloudfunctions.net/get_sentiment_sample'
    )

いざ実行

もろもろ準備が整ったので、リモート関数で感情分析結果を取得してみます。

まずは、以下の SQL を実行して、レビューコメントの感情分析してみます。

SELECT dataset_1.get_sentiment_sample(comment) AS nl_ret FROM dataset_1.review

実行結果は以下になりました。

Natural Language API レスポンスの magnitude では感情の強さ、score でポジティブな文章か、ネガティブな文章化が判断できます。

感情分析のレスポンスフィールド｜ Cloud Natural Language ドキュメント

結果をみてみると、良いレビューにはプラスのスコア、悪いレビューにはマイナスのスコアが付き、おおむね期待通りの結果が取れているようです。ただ、やはり感情分析なので、感情的ではなく理論的にネガティブな結果を伝える5行目のような文章だと、ネガティブ判断は難しいようです。

では続いて、以下のSQL を実行して、スコアの値を判定してご立腹度の高いお客様を調べてみます。

WITH ret_remote AS (
    SELECT
        user_id,
        shop_no,
        JSON_QUERY(dataset_1.get_sentiment_sample(comment), '$.score') AS score,
        comment
    FROM
        dataset_1.review
)
SELECT
    user_id,
    shop_no,
    score,
    CASE WHEN CAST(score AS FLOAT64) < -0.5 THEN '激おこ' ELSE 'おこ' END AS oko,
    comment
FROM
    ret_remote
WHERE
    CAST(score AS FLOAT64) < 0
ORDER BY CAST(score AS FLOAT64)